package com.iris.live.services.data.repositories.impl;

import com.google.common.collect.Maps;
import com.iris.live.services.common.GetConfigureInfoUtils;
import com.iris.live.services.data.repositories.CrmTopicSQLRepository;
import com.iris.live.services.models.PageData;
import com.iris.live.services.services.CRMService;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * Created by lusong on 2016/11/11.
 */
@Repository
@Transactional//(readOnly = true)
public class CrmTopicSQLRepositoryImpl extends BaseRepository implements CrmTopicSQLRepository {

    /**
     * 根据 permit 和 调研阶段 查询当前使用的题库及答案
     * @param permit
     * @param stage
     * @return
     */
    public List<CRMService.GetCurrentTopic> topicCurrentQuery(String permit, String stage) {
        StringBuffer sb = new StringBuffer();
        sb.append(" SELECT  ");
        sb.append(" 	crm_topic.id,  ");
        sb.append(" 	case when crm_topic.type = '01' then '单选'  ");
        sb.append(" 	when crm_topic.type = '02' then '多选'  ");
        sb.append(" 	when crm_topic.type = '03' then '开放'  ");
        sb.append(" 	else '' end type,  ");
        sb.append(" 	crm_topic.stage,  ");
        sb.append(" 	crm_topic.number,  ");
        sb.append(" 	crm_topic.lead,  ");
        sb.append(" 	crm_topic.stem,  ");
        sb.append(" 	crm_topic.forward,  ");
        sb.append(" 	crm_topic.version, ");
        sb.append(" 	crm_topic.remark  ");
        sb.append(" FROM crm_topic AS crm_topic ");
        sb.append(" JOIN (  ");
        sb.append(" 				SELECT  ");
        sb.append(" 					MAX(b.version) version ");
        sb.append(" 				FROM  ");
        sb.append(" 					crm_topic b  ");
        sb.append(" 				WHERE  ");
        sb.append(" 					b.stage =:stage  ");
        sb.append(" 					and b.permit =:permit ");
        sb.append(" 					and b.version like :permitLike ");
        sb.append(" 				GROUP BY  ");
        sb.append(" 					stage  ");
        sb.append(" 			) AS crm_topic_max ");
        sb.append(" ON crm_topic.version = crm_topic_max.version ");
        sb.append(" order by crm_topic.id ");
        Map params = Maps.newHashMap();
        params.put("permit",permit);
        params.put("stage",stage);
        if (GetConfigureInfoUtils.getConfigureInfo("file_group").equals(permit)) {
            params.put("permitLike", "%DCH%");
        } else {
            params.put("permitLike", "%" + permit + "%");
        }
        return this.getList(sb.toString(),params);
    }


    public List<Object> queryTopicForSurvey(String permit,String stage){
        StringBuffer sb = new StringBuffer();
        sb.append(" SELECT ");
        sb.append(" 	n.id, ");
        sb.append(" 	n.type, ");
        sb.append(" 	n.stage, ");
        sb.append(" 	n.number, ");
        sb.append(" 	n.lead, ");
        sb.append(" 	n.stem, ");
        sb.append(" 	n.forward, ");
        sb.append(" 	n.remark, ");
        sb.append(" 	n.version ");
        sb.append(" FROM ");
        sb.append(" 	( ");
        sb.append(" 		SELECT ");
        sb.append(" 			* ");
        sb.append(" 		FROM ");
        sb.append(" 			crm_topic a ");
        sb.append(" 		WHERE ");
        sb.append(" 			a.version = ( ");
        sb.append(" 				SELECT ");
        sb.append(" 					MAX(b.version) ");
        sb.append(" 				FROM ");
        sb.append(" 					crm_topic b ");
        sb.append(" 				WHERE ");
        sb.append(" 					b.stage =:stage ");
        sb.append(" 					and b.permit =:permit ");
        sb.append(" 					and b.version =:permitLike ");
        sb.append(" 				GROUP BY ");
        sb.append(" 					stage ");
        sb.append(" 			) ");
        sb.append(" 	) n ");
        sb.append(" where n.permit =:permit ");
        Map params = new HashMap();
        params.put("permit",permit);
        params.put("stage",stage);
        if (GetConfigureInfoUtils.getConfigureInfo("file_group").equals(permit)) {
            params.put("permitLike", "%DCH%");
        } else {
            params.put("permitLike", "%" + permit + "%");
        }
        return  this.getList(sb.toString(),params);
    }

    @Override
    public PageData getComplaintsList(String license, Integer pageIndex, Integer pageSize) {
        String sql = "SELECT a.id, a.source AS 投诉来源, a.date AS 投诉日期, a.logUser AS 投诉记录人, b.`顾客姓名` AS 客户姓名, \n" +
                "b.电话号码 AS 客户电话, b.`上次进店日期` AS 上次到店日期, c.人员姓名 AS 销售顾问, a.target AS 投诉对象,a.content AS 投诉内容\n" +
                "FROM crm_complaint a \n" +
                "LEFT JOIN `顾客记录汇总` b ON a.custId = b.`顾客编号`\n" +
                "LEFT JOIN `用户` c ON c.用户名=b.所属销售顾问 \n" +
                "WHERE a.feedbackDate IS NULL AND a.feedbackAccount IS NULL AND a.permit =:permit ORDER BY a.date DESC";
        Map map = Maps.newHashMap();
        map.put("permit", license);
        return this.getPage(sql, map, pageSize, pageIndex);
    }

    public int findCurrentVersionNum(String stage, String currentDate) {
        String sb = " select count(b.version) cou " +
                " from ( " +
                "   select version from crm_topic a " +
                "   where a.stage =:stage  " +
                "   and a.version like :currentDate " +
                "   group by version " +
                " ) b ";
        Map param = new HashMap();
        param.put("stage", stage);
        param.put("currentDate", currentDate);
        return this.getCount(sb, param);
    }

    @Override
    public List findVersion(String permit) {
        StringBuffer sb = new StringBuffer();
        sb.append(" select crm_topic.version from crm_topic as crm_topic ");
        sb.append(" where crm_topic.permit = :permit group by crm_topic.version ");
        Map params = new HashMap();
        params.put("permit", permit);
        return this.getList(sb.toString(), params);
    }

}
